﻿using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data.OleDb;
using System.Data;
using System.Data.Common;
using Vbyte.SharpOrm.Config;

namespace Vbyte.SharpOrm.SqlDb
{
    /// <summary>
    /// Access数据库实现
    /// </summary>
    public class Access : SqlDbBase
    {
        /// <summary>
        /// Initializes a new instance of the <see cref="Access"/> class.
        /// </summary>
        public Access()
        { 
        
        }

        /// <summary>
        /// 通过连接字符串建立Access数据库实例
        /// </summary>
        /// <param name="connStr">连接字符串</param>
        public Access(string connStr)
        {
            ConnectionString = connStr;
        }

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public override string ConnectionString 
        {
            get
            {
                return base.ConnectionString;
            }
            set
            {
                base.ConnectionString = value;
                Initialize();
            }
        }

        private OleDbConnection conn = null;

        /// <summary>
        /// 获取该数据的存储路径
        /// </summary>
        public string FilePath { get; set; }

        private void Initialize()
        {
            OleDbConnectionStringBuilder cb = new OleDbConnectionStringBuilder(ConnectionString);
            FilePath = cb.DataSource;
            CheckConnection();
        }

        private void CheckConnection()
        {
            conn = (CurrentConnection == null) ? new OleDbConnection() : (OleDbConnection)CurrentConnection;
            conn.ConnectionString = ConnectionString;
            if (conn.State != ConnectionState.Open) conn.Open();
        }

        /// <summary>
        /// 判定是否存在数据库
        /// </summary>
        /// <param name="dbname">数据库名或路径</param>
        /// <returns></returns>
        public override bool ExsitsDb(string dbname)
        {
            return File.Exists(dbname);
        }

        /// <summary>
        /// 创建数据库
        /// </summary>
        /// <param name="dbname">数据库名或文件路径</param>
        public override void CreateDb(string dbname)
        {
            string result = JetAccess.CreateMDB(dbname);
            if (result != "0")
            {
                throw new Exception(result);
            }
        }

        /// <summary>
        /// 创建带密码的数据库
        /// </summary>
        /// <param name="dbname">数据库名或文件路径</param>
        /// <param name="dbpwd">数据库密码</param>
        public override void CreateDb(string dbname, string dbpwd)
        {
            string result = JetAccess.CreateMDB(dbname, dbpwd);
            if (result != "0")
            {
                throw new Exception(result);
            }
        }

        /// <summary>
        /// 删除数据库
        /// </summary>
        /// <param name="dbName">数据库名或路径</param>
        public override void DropDb(string dbName)
        {
            File.Delete(dbName);
        }

        /// <summary>
        /// 判定是否存在指定表名
        /// </summary>
        /// <param name="tabName">数据库表名</param>
        /// <returns></returns>
        public override bool ExistsTable(string tabName)
        {
            bool exists = false;
            //TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	TABLE_GUID	DESCRIPTION	TABLE_PROPID	DATE_CREATED	DATE_MODIFIED
            using (DataTable sTab = conn.GetSchema("Tables"))
            {
                DataRow[] rows = sTab.Select("TABLE_NAME = '" + tabName + "'");
                exists = (rows != null && rows.Length > 0);
            }
            return exists;
        }

        /// <summary>
        /// 根据统一表定义创建表
        /// </summary>
        /// <param name="tabDefine">数据表定义</param>
        public override void CreateTable(SqlTable tabDefine)
        {
            //完善依赖关系
            if (ExistsTable(tabDefine.Name)) return;

            StringBuilder sql = new StringBuilder();
            StringBuilder sqlComment = new StringBuilder();
            sql.AppendFormat("CREATE TABLE {0}", tabDefine.Name);
            sql.AppendLine();
            sql.Append("(");
            sql.AppendLine();

            #region 建表结构及字段描述
            for (int i = 0, j = tabDefine.Columns.Length; i < j; i++)
            {
                SqlTableColumn col = tabDefine.Columns[i];
                sql.AppendFormat(" {0} {1} {2}{3}{4}{5}", col.Name,
                    col.DataType,
                    col.IsNullable ? "null" : "not null",
                    (col.IsIdentity && col.AutoIncrement != null) ? " identity" + col.AutoIncrement.ToString() : "",
                    col.Default != null ? " default " + col.Default.ToString() : "",
                    col.IsPrimary ? (tabDefine.PrimaryKeys.Length > 1 ? "" : " Primary key") : ""
                  );

                if (i < j - 1) { sql.Append(","); }
                sql.AppendLine();

                if (!string.IsNullOrEmpty(col.Description))
                {
                    //添加字段描述:TODO
                }
            }
            #endregion

            sql.AppendLine(")");
            sql.AppendLine("GO");

            #region 表索引
            foreach (SqlIndexConfig idx in tabDefine.Indexes)
            {
                sqlComment.AppendLine();
                sqlComment.AppendFormat("CREATE{0} {4} INDEX {1} ON {3}",
                    idx.IsUnique ? " UNIQUE" : "",
                    idx.IndexName,
                    "",  tabDefine.Name, "");
                sqlComment.AppendLine("(");

                for (int i = 0, j = idx.ColumnIndex.Length; i < j; i++)
                {
                    if (i == 0)
                    {
                        sqlComment.Append(idx.ColumnIndex[i].ToString());
                    }
                    else
                    {
                        sqlComment.Append("," + idx.ColumnIndex[i].ToString());
                    }
                    sqlComment.AppendLine();
                }
                sqlComment.AppendLine(")");
                sqlComment.AppendLine("WITH IGNORE NULL");
                sqlComment.AppendLine("GO");
            }
            #endregion

            #region 主键、标志键约束
            if (tabDefine.PrimaryKeys.Length > 1)
            {
                //主键聚焦索引
                sqlComment.AppendLine();
                sqlComment.AppendFormat("CREATE{0} {4} INDEX {1} ON {3}",
                    " UNIQUE", "PK_" + tabDefine.Name,
                    "",  tabDefine.Name, "");
                sqlComment.AppendLine("(");
                sqlComment.Append(string.Join(",", tabDefine.PrimaryKeys));
                sqlComment.AppendLine(")");
                sqlComment.AppendLine("WITH DISALLOW NULL");
                sqlComment.AppendLine("GO");
            }

            if (tabDefine.IdentityKeys.Length > 1)
            {
                //唯一的非聚焦(聚焦、如果可能)索引
                sqlComment.AppendLine();
                sqlComment.AppendFormat("CREATE{0} {4} INDEX {1} ON {3}",
                    " UNIQUE",
                    "IDX_" + tabDefine.Name,
                    "",  tabDefine.Name, "");
                sqlComment.AppendLine("(");
                sqlComment.Append(string.Join(",", tabDefine.PrimaryKeys));
                sqlComment.AppendLine(")");
                sqlComment.AppendLine("WITH DISALLOW NULL");
                sqlComment.AppendLine("GO");
            }
            #endregion

            #region 约束(主要为外键)
            foreach (SqlConstraint ct in tabDefine.Constains)
            {
                sqlComment.AppendLine();
                sqlComment.AppendFormat("ALTER TABLE {0} ADD CONSTRAINT ", tabDefine.Name);
                sqlComment.AppendLine();
                sqlComment.AppendLine(ct.ToString()
                    .Replace("[table]", tabDefine.Name));
                sqlComment.AppendLine("GO");
            }
            #endregion

            //Console.WriteLine(sql.ToString() + sqlComment.ToString());

            #region 执行建表语句
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            string lineStr;
            StringReader reader = new StringReader(sql.ToString() + sqlComment.ToString());
            sql.Remove(0, sql.Length);
            while ((lineStr = reader.ReadLine()) != null)
            {
                if (lineStr == "GO")
                {
                    cmd.CommandText = sql.ToString();
                    try
                    {
                        //Console.WriteLine("执行语句：\r\n{0}", cmd.CommandText);
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception) 
                    {
                        //Console.WriteLine(exp.Message + Environment.NewLine
                        //    + exp.StackTrace);
                    }
                    sql.Remove(0, sql.Length);
                }
                else
                {
                    sql.AppendLine(lineStr);
                }
            }
            cmd.Dispose();
            #endregion

            //Console.WriteLine("创建数据表：{0}", tabDefine.Name);
        }

        /// <summary>
        /// 删除数据库中的表
        /// </summary>
        /// <param name="tabName">数据库表名</param>
        public override void DropTable(string tabName)
        {
            OleDbCommand cmd = new OleDbCommand("drop table " + tabName, conn);
            cmd.ExecuteNonQuery();
            cmd.Dispose();
        }

        /// <summary>
        /// 对表的修改，执行原生的SQL语句
        /// </summary>
        /// <param name="NativeSql">原生的SQL语句</param>
        public override void AlterTable(string NativeSql)
        {
            OleDbCommand cmd = new OleDbCommand(NativeSql, conn);
            cmd.ExecuteNonQuery();
            cmd.Dispose();
        }

        /// <summary>
        /// 判定是否存在指定的列名
        /// </summary>
        /// <param name="tabName">数据库表名</param>
        /// <param name="colName">数据表中的列名</param>
        /// <returns></returns>
        public override bool ExistsColumn(string tabName, string colName)
        {
            bool exists = true;
            string sql = string.Format("Select Top 1 {0} from {1}", tabName, colName);
            try
            {
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                object result = cmd.ExecuteScalar();
                cmd.Dispose();
            }
            catch (Exception)
            {
                exists = false;
            }
            return exists;
        }

        /// <summary>
        /// 在指定表名中添加新的列
        /// </summary>
        /// <param name="tabName">数据库表名</param>
        /// <param name="column">数据列定义</param>
        public override void AddColumn(string tabName, SqlTableColumn column)
        {
            string colSQL = string.Format(" {0} {1} {2}{3}{4}{5}", column.Name,
                    column.DataType,
                    column.IsNullable ? "null" : "not null",
                    (column.IsIdentity && column.AutoIncrement != null) ? " identity" + column.AutoIncrement.ToString() : "",
                    column.Default != null ? " default " + column.Default.ToString() : "",
                    column.IsPrimary ? " Primary key" : ""
                  );

            string sql = string.Format("alter table {0} add \r\n{1}",
                tabName, colSQL);

            AlterTable(sql);
        }

        /// <summary>
        /// 删除指定表名中的数据列
        /// </summary>
        /// <param name="tabName">数据库表名</param>
        /// <param name="colName">数据表中的列名</param>
        public override void DropColumn(string tabName, string colName)
        {
            string sql = string.Format("Alter Table {0} Drop column {1}", tabName, colName);
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            cmd.ExecuteNonQuery();
            cmd.Dispose();
        }

        /// <summary>
        /// 获取或设置该实例上的数据库连接
        /// </summary>
        /// <value>数据库连接对象</value>
        public override DbConnection CurrentConnection
        {
            get
            {
                return base.CurrentConnection;
            }

            set
            {
                if (base.CurrentConnection != null)
                {
                    if (base.CurrentConnection.State == ConnectionState.Open) base.CurrentConnection.Close();
                    base.CurrentConnection.Dispose();
                }

                base.CurrentConnection = (OleDbConnection)value;
            }
        }

        /// <summary>
        /// 执行与释放或重置非托管资源相关的应用程序定义的任务。
        /// </summary>
        public override void Dispose()
        {
            base.Dispose();
            if (conn != null)
            {
                if (conn.State != ConnectionState.Closed) conn.Close();
                conn.Dispose();
            }
        }



    }
}
